﻿IF (OBJECT_ID('fn_CF_splitarr') IS NOT NULL)
BEGIN
    DROP FUNCTION fn_CF_splitarr;
END;
GO

-- =============================================
-- Author:		<于大明>
-- Create date: <2018-09-13>
-- Description:	<说明: 分割字符成为一个表>
-- Example:  SELECT * FROM  dbo.fn_CF_splitarr('123,12321',',')
-- =============================================
CREATE FUNCTION fn_CF_splitarr
(
    @Arr AS VARCHAR(7999), -- 需要分割的字符串
    @Split  AS VARCHAR(50) --分割的字符
)
RETURNS @t TABLE
(
    pos INT NOT NULL,
    value VARCHAR(100) NOT NULL
)
AS
BEGIN
    DECLARE @end AS INT,
            @start AS INT,
            @pos AS INT;

    SELECT @Arr = @Arr + @Split,
           @pos = 1,
           @start = 1,
           @end = CHARINDEX(@Split, @Arr, @start);

    WHILE @end > 1
    BEGIN
        INSERT INTO @t
        VALUES
        (@pos, SUBSTRING(@Arr, @start, @end - @start));

        SELECT @pos = @pos + 1,
               @start = @end + 1,
               @end = CHARINDEX(@Split, @Arr, @start);
    END;

    RETURN;
END;

GO